/**  
 * 文 件 名: RightDAO.java  
 * 描    述：   
 * 版    权：Copyright (c)20012-2012 tongzhou All Rights Reserved.
 * 公    司: 同舟 
 * 作    者：彭定雄                     
 * 版    本: 1.0  
 * 创建时间: 2012-10-27
 *  
 * 修改历史：  
 * 时间                             作者                       版本                        描述  
 * ------------------------------------------------------------------  
 * 2012-10-27        彭定雄                1.0          1.0 Version  
 */        

/**
 * 
 */
package com.tongzhou.system.rightsMgmt.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.system.rightsMgmt.dao.IRightDAO;
import com.tongzhou.system.rightsMgmt.model.Right;
import com.tongzhou.system.rightsMgmt.model.SearchRightCondition;
import com.tongzhou.util.Util;

public class RightDAO implements IRightDAO{

	@Override
	public List<Right> listRight(SearchRightCondition condition)
			throws DBException {
		List<Right> result=null;
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(!Util.checkObjIsNull(condition.getName())){
			sqlCondition.append(" and r.name like ?") ;
			paramList.add("%"+condition.getName()+"%") ;
		}
		if(!Util.checkObjIsNull(condition.getActive())){
			sqlCondition.append(" and r.active=?");
			paramList.add(condition.getActive());
		}
		if(!Util.checkObjIsNull(condition.getParent_name())){
			sqlCondition.append(" and r2.name like ?");
			paramList.add("%"+condition.getParent_name()+"%");
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(*) from sys_right r ");
		sql.append(" left join sys_right r2 on r2.id=r.parent_id ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		
		//总记录数
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select r.id,r.name,r.url,r.parent_id,r2.name as parent_name,");
		sql.append("r.module_id,r.active ");
		sql.append(" from sys_right r left join sys_right r2 on r2.id=r.parent_id ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Right>(Right.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}

	@Override
	public void addRight(Right right) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into sys_right(name,url,parent_id,module_id,active) ");
		sql.append("values('"+right.getName()+"','"+right.getUrl()+"',");
		sql.append("'"+(Util.checkObjIsNull(right.getParent_id())?"0":right.getParent_id())+"',");
		sql.append("'"+(Util.checkObjIsNull(right.getModule_id())?"0":right.getModule_id())+"','"+right.getActive()+"');");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Right> checkRightByName(Right right) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select name from sys_right where name='"+right.getName()+"' and id!='"+right.getId()+"'");
		List<Right> result=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
		return result;
	}

	@Override
	public void deleteRight(Right right) throws DBException {
		StringBuffer sql=new StringBuffer();
		
		List<Right> result=null;
		sql.delete(0, sql.length());
		sql.append("select id from sys_right where parent_id='"+right.getId()+"'");
		result=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
		if(result.size()>0){
			sql.delete(0, sql.length());
			sql.append("select r2.id from sys_right r2 where exists ");
			sql.append(" (select r1.id from sys_right r1 where r1.parent_id='"+right.getId()+"' and r2.parent_id=r1.id) ");
			List<Right> result1=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
			if(result1.size()>0){
				for(Right r:result){
					sql.delete(0, sql.length());
					sql.append("select r3.id from sys_right r3 where exists( ");
					sql.append("(select r2.id from sys_right r2 where exists ");
					sql.append(" (select r1.id from sys_right r1 where r1.parent_id='"+right.getId()+"' and r2.parent_id=r1.id) ");
					sql.append(" and r2.id=r3.parent_id)) ");
					List<Right> result2=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
					if(result2.size()>0){
						for(Right r2:result1){
							sql.delete(0, sql.length());
							sql.append("delete from sys_right where parent_id='"+r2.getId()+"'");
							DBUtil.update(sql.toString());
						}
					}
					
					sql.delete(0, sql.length());
					sql.append("delete from sys_right where parent_id='"+r.getId()+"'");
					DBUtil.update(sql.toString());
				}
			}
			sql.delete(0, sql.length());
			sql.append("delete from sys_right where parent_id='"+right.getId()+"'");
			DBUtil.update(sql.toString());
		}
		sql.delete(0, sql.length());
		sql.append("delete from sys_right where id='"+right.getId()+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public void updateRight(Right right) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_right set ");
		sql.append("name='"+right.getName()+"',url='"+right.getUrl()+"',");
		sql.append("parent_id='"+(Util.checkObjIsNull(right.getParent_id())?"0":right.getParent_id())+"',");
		sql.append("module_id='"+(Util.checkObjIsNull(right.getModule_id())?"0":right.getModule_id())+"',active='"+right.getActive()+"' ");
		sql.append(" where id='"+right.getId()+"' ");
		DBUtil.update(sql.toString());
	}

	@Override
	public Right getRightById(Right right) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select r.id,r.name,r.url,r.parent_id,");
		sql.append("r.module_id,r.active ");
		sql.append(" from sys_right r ");
		sql.append(" where 1=1 ");
		sql.append(" and id='"+right.getId()+"'");
		Right right1=DBUtil.query(sql.toString(), new BeanHandler<Right>(Right.class));
		return right1;
	}

	@Override
	public List<Right> getRightListByParentID(Right right) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct r.id,r.name,r.url,r.parent_id,");
		sql.append("r.module_id,r.active ");
		sql.append(" from sys_right r ");
		sql.append(" where 1=1 ");
		sql.append(" and parent_id='"+right.getParent_id()+"'");
		List<Right> result=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
		return result;
	}
	
	//加角色后的查询子权限
	@Override
	public List<Right> getRightListByParentID2(Right right) throws DBException {
		int userID=Util.getAdminUserId();
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct r.id,r.name,r.url,r.parent_id,");
		sql.append("r.module_id,r.active ");
		sql.append(" from sys_right r inner join sys_role_right_relation r2 on r2.right_id=r.id ");
		sql.append(" inner join sys_user_role_relation su on su.role_id=r2.role_id ");
		sql.append(" where 1=1 ");
		sql.append(" and r.parent_id='"+right.getParent_id()+"'");
		sql.append(" and su.user_id='"+userID+"'");
		List<Right> result=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
		return result;
	}

	//厂商管理中厂商列表特殊权限
	@Override
	public <T> T addAccessControl(String userID,
			Map<String, String> dataMap, String sql,Object[] params,ResultSetHandler<T> rsh) throws DBException {
		Set<String> dataKeys=dataMap.keySet();
		StringBuffer sb=new StringBuffer();
		sb.append("select temp.* from ("+sql+") temp ");
		sb.append(" where 1=1");
		//区分厂商与其他条件控制
//		Map<String,String> comp=new HashMap<String, String>();
//		Map<String,String> other=new HashMap<String, String>();
		for(Iterator it=dataKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" and exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and temp."+id+"=sr.data_id and s_r.active='Y')");
//			if(value.equals("comp_company_level1")||value.equals("comp_company_level2")||value.equals("comp_company_level3")){
//				comp.put(id, value);
//			}else{
//				other.put(id, value);
//			}
		}
//		Set<String> compKeys=comp.keySet();
//		if(compKeys.size()>0){
//			sb.append(" and (");
//		}
//		for(Iterator it=compKeys.iterator();it.hasNext();){
//			String id=(String) it.next();
//			String value=dataMap.get(id);
//			sb.append(" exists (select sr.id ");
//			sb.append(" from sys_role_right_type_relation sr  ");
//			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
//			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
//			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and temp."+id+"=sr.data_id)");
//			if(it.hasNext()){
//				sb.append(" or ");
//			}
//		}
//		if(compKeys.size()>0){
//			sb.append(")");
//		}
//		Set<String> otherKeys=other.keySet();
//		for(Iterator it=otherKeys.iterator();it.hasNext();){
//			String id=(String) it.next();
//			String value=dataMap.get(id);
//			sb.append(" and exists (select sr.id ");
//			sb.append(" from sys_role_right_type_relation sr  ");
//			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
//			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
//			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and temp."+id+"=sr.data_id)");
//		}
		return DBUtil.query(sb.toString(),rsh,params);
	}
	
	//有页码的查询权限
	@Override
	public <T> T addAccessControl(String userID,
			Map<String, String> dataMap, String sql,Object[] params,ResultSetHandler<T> rsh,int startPos,int count) throws DBException {
		Set<String> dataKeys=dataMap.keySet();
		StringBuffer sb=new StringBuffer();
		sb.append("select temp.* from ("+sql+") temp ");
		sb.append(" where 1=1");
		//区分厂商与其他条件控制
		Map<String,String> comp=new HashMap<String, String>();
		Map<String,String> other=new HashMap<String, String>();
		for(Iterator it=dataKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			if(value.equals("comp_company_level1")||value.equals("comp_company_level2")||value.equals("comp_company_level3")){
				comp.put(id, value);
			}else{
				other.put(id, value);
			}
		}
		Set<String> compKeys=comp.keySet();
		if(compKeys.size()>0){
			sb.append(" and (");
		}
		for(Iterator it=compKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and temp."+id+"=sr.data_id and sr.active='Y' and s_r.active='Y')");
			if(it.hasNext()){
				sb.append(" or ");
			}
		}
		if(compKeys.size()>0){
			sb.append(")");
		}
		Set<String> otherKeys=other.keySet();
		for(Iterator it=otherKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" and( exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and temp."+id+"=sr.data_id and sr.active='Y' and s_r.active='Y')");
			if(value.equals("sys_department")){
				sb.append(" or temp.id='"+userID+"' ");
			}
			sb.append(")");
			
		}
		return DBUtil.query(sb.toString(),rsh,params,startPos,count);
	}
	
	//特殊统计查询用到
	@Override
	public <T> T addAccessControl2(String userID,Map<String,String> dataMap,
			String sql,String sql2,Object[] params,ResultSetHandler<T> rsh,int startPos,int count) throws DBException {
		Set<String> dataKeys=dataMap.keySet();
		StringBuffer sb=new StringBuffer();
		sb.append(sql);
		//区分厂商与其他条件控制
		Map<String,String> comp=new HashMap<String, String>();
		Map<String,String> other=new HashMap<String, String>();
		for(Iterator it=dataKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			if(value.equals("comp_company_level1")||value.equals("comp_company_level2")||value.equals("comp_company_level3")){
				comp.put(id, value);
			}else{
				other.put(id, value);
			}
		}
		Set<String> compKeys=comp.keySet();
		if(compKeys.size()>0){
			sb.append(" and (");
		}
		for(Iterator it=compKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and "+id+"=sr.data_id and sr.active='Y' and s_r.active='Y')");
			if(it.hasNext()){
				sb.append(" or ");
			}
		}
		if(compKeys.size()>0){
			sb.append(")");
		}
		Set<String> otherKeys=other.keySet();
		for(Iterator it=otherKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" and exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and "+id+"=sr.data_id and sr.active='Y' and s_r.active='Y')");
		}
		sb.append(sql2);
		return DBUtil.query(sb.toString(),rsh,params,startPos,count);
	}

	//获取查询总数
	@Override
	public long getAccessControlCount(String userID,
			Map<String, String> dataMap, String sql, Object[] params) throws DBException {
		Set<String> dataKeys=dataMap.keySet();
		StringBuffer sb=new StringBuffer();
		sb.append(sql);
//		sb.append(" where 1=1");
		//区分厂商与其他条件控制
		Map<String,String> comp=new HashMap<String, String>();
		Map<String,String> other=new HashMap<String, String>();
		for(Iterator it=dataKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			if(value.equals("comp_company_level1")||value.equals("comp_company_level2")||value.equals("comp_company_level3")){
				comp.put(id, value);
			}else{
				other.put(id, value);
			}
		}
		Set<String> compKeys=comp.keySet();
		if(compKeys.size()>0){
			sb.append(" and (");
		}
		for(Iterator it=compKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and "+id+"=sr.data_id and sr.active='Y' and s_r.active='Y')");
			if(it.hasNext()){
				sb.append(" or ");
			}
		}
		if(compKeys.size()>0){
			sb.append(")");
		}
		Set<String> otherKeys=other.keySet();
		for(Iterator it=otherKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" and (exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and "+id+"=sr.data_id and sr.active='Y' and s_r.active='Y')");
			if(value.equals("sys_department")){
				sb.append(" or s.id='"+userID+"' ");
			}
			sb.append(")");
		}
		
		return DBUtil.getCount(sb.toString(), params);
	}
	
	//特殊统计返回相应的sql
	@Override
	public String getAccessControlCount2(String userID,
			Map<String, String> dataMap, String sql, Object[] params) throws DBException {
		Set<String> dataKeys=dataMap.keySet();
		StringBuffer sb=new StringBuffer();
		sb.append(sql);
//		sb.append(" where 1=1");
		//区分厂商与其他条件控制
		Map<String,String> comp=new HashMap<String, String>();
		Map<String,String> other=new HashMap<String, String>();
		for(Iterator it=dataKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			if(value.equals("comp_company_level1")||value.equals("comp_company_level2")||value.equals("comp_company_level3")){
				comp.put(id, value);
			}else{
				other.put(id, value);
			}
		}
		Set<String> compKeys=comp.keySet();
		if(compKeys.size()>0){
			sb.append(" and (");
		}
		for(Iterator it=compKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" inner join sys_role s_r on s_r.id=su.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and "+id+"=sr.data_id and sr.active='Y' and s_r.active='Y')");
			if(it.hasNext()){
				sb.append(" or ");
			}
		}
		if(compKeys.size()>0){
			sb.append(")");
		}
		Set<String> otherKeys=other.keySet();
		for(Iterator it=otherKeys.iterator();it.hasNext();){
			String id=(String) it.next();
			String value=dataMap.get(id);
			sb.append(" and exists (select sr.id ");
			sb.append(" from sys_role_right_type_relation sr  ");
			sb.append(" inner join sys_right_type sr2 on sr2.id=sr.right_type_id ");
			sb.append(" inner join sys_user_role_relation su on su.role_id=sr.role_id ");
			sb.append(" where sr2.table_name='"+value+"' and su.user_id='"+userID+"' and "+id+"=sr.data_id and sr.active='Y')");
		}
		return sb.toString();
	}
}
